CodeIgniter 3 – How to connect to Azure SQL server

In CodeIgniter 3, connecting to an Azure SQL Server involves configuring the database settings in the database.php configuration file. Here are the steps to connect CodeIgniter 3 to Azure SQL Server:

Step 1: Install CodeIgniter 3

If you haven’t already installed CodeIgniter 3, download it from the official website (https://codeigniter.com/download) or via Composer.

Step 2: Configure Database Settings

Navigate to application/config/database.php in your CodeIgniter 3 project and configure the database settings

$db['default'] = array(
    'dsn'      => '',
    'hostname' => 'your-server.database.windows.net',
    'username' => 'your-username',
    'password' => 'your-password',
    'database' => 'your-database',
    'dbdriver' => 'sqlsrv', // Use SQL Server driver
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt'  => TRUE, // Azure SQL Server usually requires encryption
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'port'     => 1433, // Azure SQL Server port
);

Replace 'your-server.database.windows.net', 'your-username', 'your-password', 'your-database' with your Azure SQL Server credentials and database information.

Step 3: Enable SQLSRV Driver

Ensure that the PHP SQLSRV driver is installed and enabled on your server. You might need to install it via PECL or enable it in your PHP configuration file.

Step 4: Verify Connection

Create a controller or a simple test script to check the database connection

class TestController extends CI_Controller {
    public function index() {
        $this->load->database();

        if ($this->db->conn_id->connect_error) {
            die("Connection failed: " . $this->db->conn_id->connect_error);
        }

        echo "Connected successfully";
    }
}

Then, visit the corresponding URL mapped to this controller method in your browser to check if the connection to the Azure SQL Server database is successful.

Step 5: Error Handling and Security

Ensure proper error handling and security measures in your CodeIgniter application. Use environment-specific configuration files and avoid hardcoding sensitive information.

Additional Considerations

Ensure your Azure SQL Server firewall rules allow access from your application server’s IP address.
Azure SQL Server might require additional setup or configurations, such as setting up the firewall rules and enabling the appropriate features for external access.

Always handle database credentials and sensitive information securely to prevent unauthorized access to your Azure SQL Server.